/*

CREATE SEQUENCE seq_member_no start with 1 increment by 1

select * from board where b_subject not like 'reply';
select * from xiegbe where x_id='1'

select * from board;
select * from category;
select * from menu;
select * from study;
select * from title;
select * from member;

delete from board where b_no=43;

select * from board where cate_no=7

CREATE table xiegbe;


CREATE TABLE xiegbe (
       x_id            integer not null,
       x_co            integer,
       x_temp          integer
);
ALTER TABLE xiegbe
       ADD  ( PRIMARY KEY (x_id) ) ;


select * from xiegbe;

insert into xiegbe values(4,64,88);
insert into xiegbe values(5,55,66);
insert into xiegbe values(6,66,77);

drop table xiegbe cascade constraint;
-----SEQUENCE����-----
DROP SEQUENCE seq_board_no;
DROP SEQUENCE seq_category_no;
DROP SEQUENCE seq_menu_no;
DROP SEQUENCE seq_study_no;
DROP SEQUENCE seq_title_no;
DROP SEQUENCE seq_member_no;


select * from zipcode where dong like '%�Ｚ%��';

-----Table����-----
drop table board cascade constraint;
drop table category cascade constraint;
drop table menu cascade constraint;
drop table study cascade constraint;
drop table title cascade constraint;
drop table member cascade constraint;


delete from board;
delete from board;
select * from notice;



-----SEQUENCE��-----

CREATE SEQUENCE seq_board_no 
 INCREMENT BY 1 
 START WITH 1 
;
CREATE SEQUENCE seq_category_no 
 INCREMENT BY 1 
 START WITH 1 
;
CREATE SEQUENCE seq_menu_no 
 INCREMENT BY 1 
 START WITH 1 
;
CREATE SEQUENCE seq_study_no 
 INCREMENT BY 1 
 START WITH 1 
;
CREATE SEQUENCE seq_title_no 
 INCREMENT BY 1 
 START WITH 1 
;
CREATE SEQUENCE seq_member_no 
 INCREMENT BY 1 
 START WITH 1 
;

----���������----
PURGE RECYCLEBIN

select * from tab;


------------------------------------------------------------------------

--------board���̺��!!!----------

CREATE TABLE board (
       b_no                 INTEGER NOT NULL,
       b_subject            VARCHAR2(50) NOT NULL,
       b_content            VARCHAR2(4000) NOT NULL,
       b_writer             VARCHAR2(20) NOT NULL,
       b_writedate          DATE NOT NULL,
       b_readhit            INTEGER NOT NULL,
       b_ip                 VARCHAR2(50) NOT NULL,
       b_filename           VARCHAR2(200) NOT NULL,
       b_ref                INTEGER NOT NULL,
       b_step               INTEGER NOT NULL,
       b_depth              INTEGER NOT NULL,
       b_reply_count        INTEGER NOT NULL,
       b_notice				INTEGER NOT NULL,
       menu_no              INTEGER NOT NULL,
       cate_no              INTEGER NOT NULL,
       title_no             INTEGER NOT NULL
);




update board set b_ref=0 where b_no=1

//�ߺ��ɼ� ����! no primaryŰ�� ����!
ALTER TABLE board
       ADD  ( PRIMARY KEY (b_no) ) ;


------------------------------------------------------------------------

--------category���̺��!!!----------

CREATE TABLE category (
       cate_no              INTEGER NOT NULL,
       cate_name            VARCHAR2(30) NOT NULL,
       menu_no              INTEGER NOT NULL,
       cate_step            INTEGER NOT NULL
);


ALTER TABLE category
       ADD  ( PRIMARY KEY (cate_no) ) ;
       
 
select * from category      
insert into category values(
							seq_category_no.nextVal,
							'공지사항',
							2,
							8
							) 
							      

------------------------------------------------------------------------

--------member���̺��!!!----------

CREATE TABLE member (
       m_no                 INTEGER NOT NULL,
       m_name               VARCHAR2(50) NOT NULL,
       m_id                 VARCHAR2(50) UNIQUE,
       m_pwd                VARCHAR2(50) NOT NULL,
       m_birth_y            VARCHAR2(50) NOT NULL,
       m_birth_m            VARCHAR2(50) NOT NULL,
       m_birth_d            VARCHAR2(50) NOT NULL,
       m_pwd_hint           VARCHAR2(100) NOT NULL,
       m_pwd_answer         VARCHAR2(50) NOT NULL,
       m_sex                VARCHAR2(10) NOT NULL,
       m_mail               VARCHAR2(50) NOT NULL,
       m_hp                 VARCHAR2(50) NOT NULL,
       m_alias              VARCHAR2(50) UNIQUE,
       m_zipcode            VARCHAR2(50) NOT NULL,
       m_addr1              VARCHAR2(100) NOT NULL,
       m_addr2              VARCHAR2(50) NOT NULL,
       m_join_date			date,	
       g_no					INTEGER NOT NULL    
);

select * from member;
delete from member where m_name='fff';
------------------------------------------------------------------------

--------grade���̺��!!!----------

DROP SEQUENCE seq_grade_no;


drop table grade cascade constraint;
drop table category cascade constraint;
drop table board cascade constraint;
drop table member cascade constraint;
drop table menu cascade constraint;
drop table study cascade constraint;
drop table study_state cascade constraint;
drop table title cascade constraint;
drop table xiegbe cascade constraint;



CREATE SEQUENCE seq_grade_no 
 INCREMENT BY 1 
 START WITH 1
;
       
create table grade(
	g_no	INTEGER NOT NULL ,
	g_name	VARCHAR2(50) NOT NULL
);

ALTER TABLE grade
       ADD  ( PRIMARY KEY (g_no) ) ;


  ALTER TABLE member
       ADD  ( FOREIGN KEY (g_no)
                             REFERENCES grade ) ;                        


drop

insert into grade values(
1,'일반회원');

insert into grade values(
seq_grade_no.nextVal,'우수회원');

insert into grade values(
seq_grade_no.nextVal,'신');

select * from grade;

select * from member;

delete from member where m_name='������';

update member
set m_sex='woman'
where m_name='������';

update member
set m_hp='017.1234.2800'
where m_name='�̼�ȯ';



update 


                             

							

------------------------------------------------------------------------
------------------------------------------------------------------------

--------menu테이블생성!!!----------

CREATE TABLE menu (
       menu_no              INTEGER NOT NULL,
       menu_name            VARCHAR2(30) NOT NULL,
       menu_step            INTEGER NOT NULL
);


ALTER TABLE menu
       ADD  ( PRIMARY KEY (menu_no) ) ;



------------------------------------------------------------------------

--------study테이블생성!!!----------

CREATE TABLE study (
       s_no                 INTEGER NOT NULL,
       s_subject            VARCHAR2(50) NOT NULL,
       s_content            VARCHAR2(4000) NOT NULL,
       s_writer             VARCHAR2(30) NOT NULL,
       s_write_date         DATE NOT NULL,
       s_filename           VARCHAR2(200) NOT NULL,
       title_no             INTEGER NOT NULL,
       s_step               INTEGER NOT NULL,
       menu_no              INTEGER NOT NULL,
       cate_no              INTEGER NOT NULL
);


ALTER TABLE study
       ADD  ( PRIMARY KEY (s_no) ) ;


------------------------------------------------------------------------

--------study_state테이블생성!!!----------

CREATE TABLE study_state (
       state_no             INTEGER NOT NULL,
       m_no                 INTEGER NOT NULL,
       s_no                 INTEGER NOT NULL,
       state_rate           LONG NOT NULL
);


ALTER TABLE study_state
       ADD  ( PRIMARY KEY (state_no) ) ;



------------------------------------------------------------------------

--------title테이블생성!!!----------

CREATE TABLE title (
       title_no             INTEGER NOT NULL,
       title_name           VARCHAR2(30) NOT NULL,
       cate_no              INTEGER NOT NULL,
       title_step           INTEGER NOT NULL
);


ALTER TABLE title
       ADD  ( PRIMARY KEY (title_no) ) ;


------------------------------------------------------------------------







--데이터 추가--
--menu--

select * from menu;
insert into menu values(
			seq_menu_no.nextVal,
			'STUDY', seq_menu_no.currVal);

insert into menu values(
			seq_menu_no.nextVal,
			'관리자', seq_menu_no.currVal);						
update menu set menu_name='STUDY' where menu_name='COMMUNITY'
			
delete from category;	
		
--category--
insert into category values(seq_category_no.nextVal,'Java Programing',1,seq_category_no.currVal);
insert into category values(seq_category_no.nextVal,'Web Programing',1,seq_category_no.currVal);
insert into category values(seq_category_no.nextVal,'Mobile Programing',1,seq_category_no.currVal);


insert into category values(seq_category_no.nextVal,'전체 게시판',2,seq_category_no.currVal);
insert into category values(seq_category_no.nextVal,'공지사항',2,seq_category_no.currVal);
insert into category values(seq_category_no.nextVal,'지역 게시판',2,seq_category_no.currVal);
insert into category values(seq_category_no.nextVal,'그룹 게시판',2,seq_category_no.currVal);

--title--
insert into title values(seq_title_no.nextVal,'변수',5,seq_title_no.currVal);
insert into title values(seq_title_no.nextVal,'연산자',5,seq_title_no.currVal);
insert into title values(seq_title_no.nextVal,'제어문',5,seq_title_no.currVal);

insert into title values(0,'no_title',8,0)


select * from category order by cate_step asc



*****************************************************


ALTER TABLE board
       ADD  ( FOREIGN KEY (title_no)
                             REFERENCES title ) ;


ALTER TABLE board
       ADD  ( FOREIGN KEY (cate_no)
                             REFERENCES category ) ;


ALTER TABLE board
       ADD  ( FOREIGN KEY (menu_no)
                             REFERENCES menu ) ;


ALTER TABLE category
       ADD  ( FOREIGN KEY (menu_no)
                             REFERENCES menu ) ;


ALTER TABLE study
       ADD  ( FOREIGN KEY (cate_no)
                             REFERENCES category ) ;

ALTER TABLE study
       ADD  ( FOREIGN KEY (menu_no)
                             REFERENCES menu ) ;


ALTER TABLE study
       ADD  ( FOREIGN KEY (title_no)
                             REFERENCES title ) ;


ALTER TABLE study_state
       ADD  ( FOREIGN KEY (s_no)
                             REFERENCES study ) ;






*/
*/